from neural_network import skynet
import time
import urlparse
import urllib2
import re
from pysqlite2 import dbapi2 as sqlite
from BeautifulSoup import BeautifulSoup

#TODO: Use SQLAlchemy instead of making the queries by hand
#TODO: Add a web service to this, so it is easier to search
#TODO: Use a faster DB than sqlite

class Crawler:
    '''A web crawler, given a DB with tables made by Crawler.create_tables
    can traverse the web a given depth and some initial pages'''
    
    ignore_words = ['the', 'of', 'to', 'and', 'a', 'in', 'is', 'it']
    
    def __init__(self, dbname):
        self.con = sqlite.connect(dbname)
    
    def __del__(self):
        '''Closes the database connection'''
        self.con.close()
    
    def dbcommit(self):
        '''Commits changes in the database'''
        self.con.commit()
    
    def get_or_create_entry(self, table, field, value, create_new=True):
        ''' Function for getting an entry id and adding it if not present'''
        select_query = "select rowid from %s where %s='%s'" % (table, field, value)
        cur = self.con.execute(select_query)
        result = cur.fetchone()
        # If it already exists, just return the result
        if result is not None: return result[0]
        # If it doesnt exist, add it then return the result
        insert_query = "insert into %s (%s) values ('%s')" % (table, field, value)
        cur = self.con.execute(insert_query)
        return cur.lastrowid   
    
    def add_to_index(self, url, soup):
        '''Given some url and the soup of the url, adds the url to the DB
        along with the associated text, returning the url id in the DB'''
        if self.is_indexed(url): 
            return self.get_or_create_entry('urllist', 'url', url)
        
        print "Indexing %s" % url
        text = self.get_text_only(soup)
        words = self.separate_words(text)
        urlid = self.get_or_create_entry('urllist', 'url', url)
        
        for i, word in enumerate(words):
            if word in self.ignore_words: continue
            wordid = self.get_or_create_entry('wordlist', 'word', word)
            self.con.execute("insert into wordlocation(urlid, wordid, location) \
                              values (%d,%d,%d)" % (urlid, wordid, i))
        return urlid
            
    def get_text_only(self, soup):
        '''Given some soup, returns only the text in it. Ignores html code, tags,
        attributes, etc.'''
        string = soup.string # Only the last nodes have a string, else it is None
        if string is None:
            result_text = []
            for element in soup.contents:
                subtext = self.get_text_only(element)
                result_text.append(subtext)
            return ''.join(result_text)
        else: # Last node, just return the string
            return string.strip()
    
    def separate_words(self, text):
        '''Separates the words in a text'''
        splitter = re.compile(r'\W*')
        return [s.lower() for s in splitter.split(text) if s != '']
    
    def is_indexed(self, url):
        '''Determines if an url is already indexed in the DB'''
        url_query = "select rowid from urllist where url='%s'" % url
        result = self.con.execute(url_query).fetchone()
        # If we have an url id registered, check if it does have indexed data
        if result is not None:
            words_query = "select * from wordlocation where urlid='%d'" % result[0]
            result = self.con.execute(words_query)
            return result is not None
        return False 
            
    
    def add_link_ref(self, url_from, url_to, link_text):
        '''Add a link between two pages'''
        words = self.separate_words(link_text)
        fromid = self.get_or_create_entry('urllist', 'url', url_from)
        toid = self.get_or_create_entry('urllist', 'url', url_to)
        if fromid == toid: return # Linking to itself
        insert_query = "insert into link(fromid,toid) values (%d,%d)" % (fromid,toid)
        cursor =self.con.execute(insert_query)
        linkid = cursor.lastrowid
        for word in words:
            if word in self.ignore_words: continue
            wordid = self.get_or_create_entry('wordlist', 'word', word)
            self.con.execute("insert into linkwords(linkid,wordid) values (%d,%d)" % (linkid,wordid))
        return linkid
    
    def crawl(self, pages, depth=5):
        '''Given a list of pages, crawl them a given depth using a breath first
        search style'''
        # Repeat depth times
        for depth_count in xrange(depth):
            found_pages = set() # Set so we dont have repeated elements
            total_pages = len(pages)
            for page_number, page in enumerate(pages):
                print "Page %d of %d in level %d" %(page_number, total_pages, depth_count)
                response = self.open_url(page) # Try opening the page, if we don't have
                if not response: continue # any result, continue with the next page
                # Try making the soup of the response contents, if there are 
                # errors in decoding the data, just skip the url
                try: soup = BeautifulSoup(response.read())
                except UnicodeDecodeError: continue
                self.add_to_index(page, soup)
                # Obtain the links
                links = soup('a')
                # Traverse the links
                for link in links:
                    link_attrs = dict(link.attrs)
                    if 'href' in link_attrs:
                        link_url = urlparse.urljoin(page, link['href'])
                        # Continue to the next link if there is an attempt
                        # to mess with the DB.
                        if self.is_sql_injection_attempt(link_url): continue
                        if link_url[0:4] == 'http' and not self.is_indexed(link_url):
                            found_pages.add(link_url)
                        link_text = self.get_text_only(link)
                        self.add_link_ref(page, link_url, link_text)
                    self.dbcommit()
            pages = found_pages
                                            
    def is_sql_injection_attempt(self, url):
        return "'" in url # Pretty lame detection atm
            
    def open_url(self, url, attempts=3, sleep_time=0.1):
        '''Open a page and return the response, if the page fails to open, try a 
        certain number of attempts, sleeping some time between them'''
        for _ in range(attempts):
            try:
                return urllib2.urlopen(url)
            except:
                print "Could not open %s" % url
                time.sleep(sleep_time) # Sleep half a second
                continue # Try again
        #return None             
    
    def create_tables(self):
        '''Creates the DB tables, this only needs to be executed once'''
        # Url list  table
        self.con.execute("create table urllist(url)")
        self.con.execute("create index urlidx on urllist(url)")
        # Word List table
        self.con.execute("create table wordlist(word)")
        self.con.execute("create index wordidx on wordlist(word)")
        # Word Location table
        self.con.execute("create table wordlocation(urlid,wordid,location)")
        self.con.execute("create index wordurlidx on wordlocation(wordid)")
        # Links Table
        self.con.execute("create table link(fromid integer,toid integer)")
        self.con.execute("create index urltoidx on link(toid)")
        # Link Words Table
        self.con.execute("create table linkwords(wordid,linkid)")
        self.con.execute("create index urlfromidx on link(fromid)")
        self.dbcommit()
            
class Searcher:
    
    def __init__(self, dbname):
        self.con = sqlite.connect(dbname)
        self.skynet = skynet('skynet.db')
    
    def __del__(self):
        self.con.close()
        
    def get_url_name(self, urlid):
        '''Given a urlid gets the url name, if it doesn't exist it returns None'''
        
        query = "select url from urllist where rowid=%d" % urlid
        cursor = self.con.execute(query)
        result = cursor.fetchone()
        return result[0] if result else None

    def get_ordered_results(self, textual_query):
        '''Returns the same as in get_match_rows, the only difference is that
        this function DOES rank the results according to a scoring function 
        (TODO: define the scoring function'''
        rows, wordids = self.get_match_rows(textual_query)
        scores = self.get_scored_urls(rows, wordids)
        ranked_scores = sorted([(score, url) for url, score in scores.items()], reverse=True)
        for score, url in ranked_scores: 
            print "%f\t%s" % (score, self.get_url_name(url))
        
        return wordids,[r[1] for r in ranked_scores[0:10]]
        
    def get_match_rows(self, query):
        '''The used query is a pathethic join. 
        
        For example, given the query "python programming language" 
        we first get the wordids for each of them, let's say the ids are
        10,11,12 respectively, then we construct a query of the form
        
        select w0.urlid, w0.location, w1.location, w2.location from 
        wordlocation w0, wordlocation w1, wordlocation w2 where
        w0.urlid=w1.urlid and w1.urlid= w2.urlid and 
        w0.wordid=10 and w1.wordid=11 and wN.wordid=12
        
        w0, w1 ... wN represent the wordlocation table, it is joined, so
        we filter it with the same urlids and then search for the words, 
        returning the urlid and the locations of each of the words.
        
        The query parameter are just words separated by spaces.
        
        This function returns a tuple of (rows, wordids) where 
        
        Rows is in the form:
        [(urlid, word1location, word2location)
         (urlid, word1location, word2location
         ... ]
         
        and wordids is just a list of word ids: 
        [word1id, word2id ... ]
        
        TODO: Use something that is not a join
        '''
        field_list = 'w0.urlid'
        table_list = ''
        clause_list = ''
        word_ids = []
        
        splitter = re.compile(r'\s')
        words = splitter.split(query)
        for word_number, word in enumerate(words):
            # Get the word id
            word_query = "select rowid from wordlist where word='%s'" % word
            word_id = self.con.execute(word_query).fetchone() # Returns None or a tuple
            if word_id is not None:
                word_id = word_id[0] # Get the numeric element
                word_ids.append(word_id)
                # Query construction in case we have more than one word in the query
                if word_number > 0: 
                    table_list += ','
                    clause_list += " and w%d.urlid=w%d.urlid and " % (word_number-1, word_number)
                field_list += ",w%d.location" % (word_number)
                table_list += "wordlocation w%d" % (word_number)
                clause_list += "w%d.wordid=%d" % (word_number, word_id)
        
        dbquery = "select %s from %s where %s" % (field_list, table_list, clause_list)
        print "Query: %s" % dbquery
        cursor = self.con.execute(dbquery)
        return cursor.fetchall(), word_ids
        
    def get_scored_urls(self, rows, wordids):
        ''' Scores the urls according to some weights.
        Rows and wordids are the same as they're returned by the get_match_rows
        function.
         '''
        
        #Initialize an empty scored dict where keys=urls, values=scores
        total_scores = {}
        for row in rows: total_scores[row[0]] = 0.0
        # The most important information for scoring the results
        # Weight indicates how much a given scoring_function affects the final score
        # The list that goes along with weight is dict of urls, scores.
        # TODO: Neural Network
        weights = [(0.8, self.nnscore(rows, wordids)),
                   (0.4, self.pagerank_score(rows)),
                   (0.8, self.link_text_score(rows, wordids)),
                   (0.7, self.distance_score(rows)),
                   (0.6, self.location_score(rows)),
                   (0.4, self.frequency_score(rows)),
                   (0.6, self.inbound_link_score(rows)),
                  ]
        
        for (weight, scores) in weights:
            for url in total_scores:
                total_scores[url] += weight * scores[url]
        
        return total_scores
        
    def frequency_score(self, rows):
        '''Given some rows, just counts how many time a given urlid appears in
        them, this works as a word frequency count.
        
        This metric considers a higher frequency as a better result.
        
        Rows are considered as returned by get_match_rows'''
        counts = {}
        for row in rows:
            urlid = row[0]
            counts.setdefault(urlid, 0.0)
            counts[urlid] += 1
            
        return self.normalize_scores(counts, high_is_better=True)

    def location_score(self, rows):
        '''Given some rows, with the urlids and each of the word locations, sums
        up the locations as a score.
        
        This metric considers that word locations near the beginning of the document
        are better than those far away. That means that if the words are in the title
        of the page, the score will be better than if the words are in someplace else.
        
        Rows are considered as returned by get_match_rows'''
        
        scores ={}
        for row in rows:
            urlid, locations = row[0], row[1:]
            old_location_sum = scores.get(urlid, None)
            new_location_sum = sum(locations)
            scores[urlid] = min(old_location_sum, new_location_sum) if old_location_sum \
            else new_location_sum
                
        return self.normalize_scores(scores, high_is_better=False)
    
    def distance_score(self,rows):
        '''Given some rows, with the urlids and each of the word location, calculates
        the distance between words.
        
        This metrics consider that words near each other have a highet score.
        
        Rows are considered as returned by get_match_rows''' 
        # If there's only one word, every row has max score
        if len(rows[0])<=2: return dict([(row[0],1.0) for row in rows])
        # Initialize the dictionary with large values
        scores = {}
        for row in rows:
            urlid = row[0]
            old_dist = scores.get(urlid,None)
            new_dist = sum([abs(row[i]-row[i-1]) for i in range(2, len(row))])
            scores[urlid] = min(old_dist, new_dist) if old_dist else new_dist
                
        return self.normalize_scores(scores,high_is_better=False)
    
    def inbound_link_score(self, rows):
        '''Given some rows as returned by get_match_rows, calculates the
        inbound links of every url in the rows.'''
        scores = {}
        
        for row in rows:
            urlid = row[0]
            # If we have already done this url, go to the next
            if urlid in scores: continue 
            # Extract the inbound links to this page
            inbound_query = "select count(*) from link where toid=%d" % urlid
            cursor = self.con.execute(inbound_query)
            num_inbound = cursor.fetchone()[0]
            scores[urlid] = num_inbound                
        return self.normalize_scores(scores, high_is_better=True)
    
    def link_text_score(self,rows,wordids):
        '''Given some rows and wordids as returned by get_match_rows, returns
        the score of the pages. If a page has incoming links that contain
        the words the page gets a better result than a page that does not'''
        # Create the url, score table initializing it with zero
        scores = dict([(row[0],0) for row in rows])
        for wordid in wordids:
            query = "select link.fromid, link.toid from linkwords, link where \
            wordid=%d and linkwords.linkid=link.rowid" % wordid
            cur=self.con.execute(query)
            for (fromid,toid) in cur:
                if toid in scores:
                    pr_query = "select score from pagerank where urlid=%d" % fromid
                    pr=self.con.execute(pr_query).fetchone()[0]
                    scores[toid] += pr
        return self.normalize_scores(scores, high_is_better=True)
    
    def pagerank_score(self, rows):
        '''Uses the pagerank from pagerank tables and returns a normalized score
        for the urls in the given rows'''
        pr_query = "select score from pagerank where urlid=%d"
        scores = dict([row[0],self.con.execute(pr_query % row[0]).fetchone()[0]] for row in rows)
        return self.normalize_scores(scores, high_is_better=True)
                
    def nnscore(self,rows,wordids):
        # Get unique URL IDs as an ordered list
        urlids=[urlid for urlid in set([row[0] for row in rows])]
        nnres=self.skynet.getresult(wordids,urlids)
        scores=dict([(urlids[i],nnres[i]) for i in range(len(urlids))])
        return self.normalize_scores(scores)

    def normalize_scores(self, scores, high_is_better=True):
        '''Given a dict with urls and scores, it normalizes the scores knowing
        if a highscore is better or a low score is better.
        
        The final result for a normalization is a number between 0 and 1, where
        1 is the best score'''
        
        # Number to avoid division by zero
        VERY_LOW_NUM = 1.0e-9
        normalized_scores = {}        
        if high_is_better:
            maximum = float(max(max(scores.values()), VERY_LOW_NUM))
            for url, score in scores.items():
                normalized_scores[url] = score/maximum
        else:
            minimum = float(min(scores.values()))
            for url, score in scores.items(): 
                normalized_scores[url] = minimum/max(score, VERY_LOW_NUM)
        
        return normalized_scores
    
    def calculate_pagerank(self, iterations=10, verbose=False):
        '''Calculates the page rank of each page, creating a new table
        with all the page ranks if they already exist. 
        
        To read more about the pagerank algorithm go to http://en.wikipedia.org/wiki/PageRank'''
        # Delete old pagerank info
        self.con.execute("drop table if exists pagerank")
        self.con.execute("create table pagerank(urlid primary key, score)")
        # Initialize all pages with a PageRank of 1
        self.con.execute("insert into pagerank select rowid, 1.0 from urllist")
        self.con.commit()
        
        for i in xrange(iterations):
            print "Iteration %d" % i
            urlids = self.con.execute("select rowid from urllist").fetchall()
            for (urlid, ) in urlids:
                pr = 0.15
                # Loop through all the pages that link to this
                linkers = self.con.execute("select fromid from link where toid=%d" % urlid).fetchall()
                for (linker,) in linkers:
                    # Get the page rank of the linker
                    pr_query = "select score from pagerank where urlid=%d" % linker
                    linker_pr = self.con.execute(pr_query).fetchone()[0]
                    links_query = "select count(*) from link where fromid=%d" % linker
                    total_links = self.con.execute(links_query).fetchone()[0]
                    pr += 0.85 * (linker_pr/total_links)
                
                # Output if we want the verbose mode of the function
                if verbose:
                    urlname = self.get_url_name(urlid)
                    old_rank = self.con.execute("select score from pagerank where urlid=%d" % urlid).fetchone()[0]
                    print "Updating rank: %s from %f to %f" % (urlname[7:], old_rank, pr) 
                # Update the pagerank in the DB 
                update_query = "update pagerank set score=%f where urlid=%d" % (pr, urlid)
                self.con.execute(update_query)
                self.con.commit()